﻿using Crawler.DBEntities;
using Crawler.WebEntities;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data;

namespace Crawler
{
    class  MysqlHelper
    {
        //private const String connStr = String.Format("server={0};user id={1}; password={2}; database=mydb; pooling=false", "localhost", "root", "123456");
        private string connStr = String.Format("server={0};user id={1}; password={2}; database=mydb; pooling=false", "localhost", "root", "123456");
        //查找所有的种子信息
        private const string sel_allSeed = @"SELECT * FROM info_seed";
        //查找特定的帖子在不在表中
        private string sel_certainPost = @"SELECT * FROM info_seed where seed_url =";
        //查看新插入的记录的id
        private string sel_lastInsert = @"select LAST_INSERT_ID()";
        /// <summary>
        /// 获取所有的种子记录
        /// </summary>
        /// <returns></returns>
        public List<Seed> GetAllSeeds()
        {
            List<Seed> seeds = new List<Seed>();
            // 建立数据库连接.
            MySqlConnection conn = new MySqlConnection(connStr);
            // 创建一个适配器
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_allSeed, conn);
            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();
            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");
            // 关闭数据库连接.
            conn.Close();
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                string seedUrl = testRow["seed_url"].ToString();
                Seed seed = new Seed(seedUrl);
                seeds.Add(seed);
            }
            return seeds;
        }
        /// <summary>
        /// 表中是否有该post
        /// </summary>
        /// <param name="postUrl"></param>
        /// <returns></returns>
        public bool IsPostExist(string postUrl)
        {
            sel_certainPost = sel_certainPost + postUrl;
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_certainPost, conn);
            DataSet testDataSet = new DataSet();
            adapter.Fill(testDataSet, "result_data");
            conn.Close();
            if (testDataSet.Tables.Count == 0)
                return false;
            else
                return true;
        }
        /// <summary>
        /// 向表中添加post记录
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public bool InsertPost(Post post)
        {
            string fk_seedid = post.Seed_post_id.ToString();
            string fk_forumid = post.Forum_post_id.ToString();
            string url = post.Url;
            string date = post.Date.ToString();
            string title = post.Title;
            string passage = post.Passage;
            string createTime = post.CreateTime.ToString();   //dateTime 的获取
            string insertPost_sql = string.Format(" INSERT INTO `mydb`.`info_post` (`FK_forum_post_id`, `FK_seed_post_id`, `post_url`,`post_date`,`post_title`,`post_passage`,`post_creatTime`) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6})",
                fk_seedid,fk_forumid,url,date,title,passage,createTime);
            bool isSucceedInsert = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                isSucceedInsert = Transaction_Post(conn, insertPost_sql);
                conn.Close();
            }
            catch (Exception)
            {
                isSucceedInsert = false;
            }
            return isSucceedInsert;
        }
        /// <summary>
        /// 获取新插入的数据的id
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private int GetInsertID(MySqlConnection conn)
        {
            int id = -1;
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_lastInsert, conn);
            DataSet testDataSet = new DataSet();
            adapter.Fill(testDataSet, "result_data");

            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                var arr = testRow.ItemArray;
                id = int.Parse(testRow["LAST_INSERT_ID()"].ToString());
            }
            return id;  
        }
        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="conn"></param>
        private bool Transaction_Post(MySqlConnection conn,string insertPost_sql)
        {
            MySqlTransaction t = conn.BeginTransaction();
            bool isTranSucceed = true;
            try
            {
                InsertPost(conn, t, insertPost_sql);
                t.Commit();
            }
            catch (Exception)
            {
                t.Rollback();
                isTranSucceed = false;
            }
            return isTranSucceed;
        }
    
        /// <summary>
        /// 插入数据.
        /// </summary>
        /// <param name="conn"></param>
        private void InsertPost(MySqlConnection conn, MySqlTransaction t,string insert_sql)
        {
            // 创建一个 Command.
            MySqlCommand insertCommand = conn.CreateCommand();

            // 定义需要执行的SQL语句.
            insertCommand.CommandText = insert_sql;

            // 注意： 只有加了这一句， 才能事务处理！！！
            insertCommand.Transaction = t;

            // 定义要查询的参数.
            //insertCommand.Parameters.Add(new MySqlParameter("?sale_date", TEST_SALE_DATE));
            //insertCommand.Parameters.Add(new MySqlParameter("?sale_item", TEST_SALE_ITEM));
            //insertCommand.Parameters.Add(new MySqlParameter("?sale_money", 100000));


            // ExecuteNonQuery 方法，表明本次操作，不是一个查询的操作。将没有结果集合返回.
            // 返回的数据，将是 被影响的记录数.
            int insertRowCount = insertCommand.ExecuteNonQuery();

            //Console.WriteLine("尝试插入数据， 结果造成了{0}条记录的插入。", insertRowCount);

        }
        /// <summary>
        /// 新纪录插入到visited表
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool InsertVisited(MyUri uri)
        {
            string ins_visited = string.Format("INSERT INTO `mydb`.`info_visited`  (`visited_url`) VALUES({0})",uri.AbsoluteUri);
            bool isSucceedInsert = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                isSucceedInsert = Transaction_Visited(conn,ins_visited);
                conn.Close();
            }
            catch (Exception)
            {
                isSucceedInsert = false;
            }
            return isSucceedInsert;
        }
        /// <summary>
        /// 插入到visited表中的数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="insertVisited_sql"></param>
        /// <returns></returns>
        private bool Transaction_Visited(MySqlConnection conn, string insertVisited_sql)
        {
            MySqlTransaction t = conn.BeginTransaction();
            bool isTranSucceed = true;
            try
            {
                InsertVisited(conn, t, insertVisited_sql);
                t.Commit();
            }
            catch (Exception)
            {
                t.Rollback();
                isTranSucceed = false;
            }
            return isTranSucceed;
        }
        /// <summary>
        /// 插入Visited数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="t"></param>
        /// <param name="insert_sql"></param>
        private void InsertVisited(MySqlConnection conn, MySqlTransaction t, string insert_sql)
        {
            // 创建一个 Command.
            MySqlCommand insertCommand = conn.CreateCommand();
            insertCommand.CommandText = insert_sql;
            insertCommand.Transaction = t;
            int insertRowCount = insertCommand.ExecuteNonQuery();
        }
        /// <summary>
        /// 判断种子是否过期
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool IsSeedTimeOut(MyUri uri)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            conn.Open();
            MySqlDataAdapter IsSeedTimeOut = new MySqlDataAdapter(string.Format(@"select seed_lastUpdateTime from info_seed where seed_url = '{0}'", uri.AbsoluteUri), conn);
            DataSet mySql = new DataSet();
            IsSeedTimeOut.Fill(mySql);
            DataTable tbl = mySql.Tables[0];
            DataRow irow = tbl.Rows[0];
            double timestamp = (double)irow[0];
            System.DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            dtDateTime = dtDateTime.AddSeconds(timestamp).ToLocalTime();
            TimeSpan d3 = DateTime.Now.Subtract(dtDateTime);
            conn.Close();

            if (d3.Days > 14)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 更新种子记录
        /// </summary>
        /// <param name="seed"></param>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool UpdateSeed(Seed seed, ref MyUri uri)
        {
            bool isUpdateSucceed = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(string.Format(@"UPDATE info_seed
SET seed_lastUpdateTime = '{0}'
WHERE seed_url='{1}'", System.DateTime.Now, uri.AbsoluteUri), conn);
                uri.Id = GetInsertID(conn);
                conn.Close();
            }
            catch (Exception)
            {
                isUpdateSucceed = false;
            }
            return isUpdateSucceed;
        }
        /// <summary>
        /// 插入种子记录
        /// </summary>
        /// <param name="seed"></param>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool InsertSeed(Seed seed,ref MyUri uri)
        {
            bool isInsertSucceed = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(string.Format(@"INSERT INTO info_seed (seed_url,seed_createTime) VALUES ('{0}','{1}')", uri.AbsoluteUri,seed.CreateTime), conn);
                uri.Id = GetInsertID(conn);
                conn.Close();
            }
            catch (Exception)
            {
                isInsertSucceed = false;
            }
            return isInsertSucceed;
        }
        /// <summary>
        /// 查找所有和所给的种子uri相关的板块uri
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public List<MyUri> SelectForums(MyUri uri)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            bool isEnqueue = true;
            List<MyUri> uris = new List<MyUri>();
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter(string.Format(@"select forum_url from info_forum,info_seed where info_forum.Fk_seed_forum_id = info_seed.seed_id and info_seed.seed_url = '{0}'", uri.AbsoluteUri), conn);
                DataTable table = new DataTable();
                da.Fill(table);
                foreach (DataRow row in table.Rows)
                {
                    MyUri url = new MyUri(row.ToString());
                    uris.Add(url);
                }
            }
            catch (Exception)
            {

            }
            return uris;
        }
        /// <summary>
        /// 板块uri是否在表中存在
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool IsForumExist(MyUri uri)
        {
            int count=0;
            MySqlConnection conn = new MySqlConnection(connStr);
            string sel_forum = string.Format("select * from info_forum where forum_url = {0}",uri.AbsoluteUri);
            try
            {
                conn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sel_lastInsert, conn);
                DataSet testDataSet = new DataSet();
                adapter.Fill(testDataSet, "result_data");
                conn.Close();


                count = testDataSet.Tables["result_data"].Rows.Count;
                
            }
            catch (Exception)
            { }
            if (count == 0)
                return false;
            else
                return true;
        }
        /// <summary>
        /// 更新板块的信息
        /// </summary>
        /// <param name="uri"></param>
        /// <param name="forum"></param>
        /// <returns></returns>
        public bool UpdateForum(ref MyUri uri,Forum forum)
        {
            bool isUpdateSucceed = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(string.Format(@"UPDATE info_forum
SET forum_lastUpdateTime = '{0}'
WHERE forum_url='{1}'", System.DateTime.Now, uri.AbsoluteUri), conn);
                uri.Id = GetInsertID(conn);
                conn.Close();
            }
            catch (Exception)
            {
                isUpdateSucceed = false;
            }
            return isUpdateSucceed;
        }
        /// <summary>
        /// 在表中插入新增的板块记录
        /// </summary>
        /// <param name="uri"></param>
        /// <param name="forum"></param>
        /// <returns></returns>
        public bool InsertForum(ref MyUri uri,Forum forum)
        {
            bool isInsertSucceed = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(
                    string.Format(@"INSERT INTO info_seed (seed_url,seed_createTime) VALUES ('{0}','{1}')", uri.AbsoluteUri, forum.CreateTime), conn);
                uri.Id = GetInsertID(conn);
                conn.Close();
            }
            catch (Exception)
            {
                isInsertSucceed = false;
            }
            return isInsertSucceed;
        }
        /// <summary>
        /// 判断帖子是否已经被访问过
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool IsVisited(MyUri uri)
        {
            int count = 0;
            MySqlConnection conn = new MySqlConnection(connStr);
            string sel_forum = string.Format("select * from info_visited where forum_url = {0}", uri.AbsoluteUri);
            try
            {
                conn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sel_lastInsert, conn);
                DataSet testDataSet = new DataSet();
                adapter.Fill(testDataSet, "result_data");
                conn.Close();
                count = testDataSet.Tables["result_data"].Rows.Count;
            }
            catch (Exception)
            { }
            if (count == 0)
                return false;
            else
                return true;
        }
    }

}
